﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL.Interfejsi;
using MySql.Data.MySqlClient;
using OOAD_PROJEKAT;

namespace DAL
{
    partial class DAL
    {
        public class KlijentDAO : IDaoCrud<Klijent>
        {

            protected MySqlCommand c;
            public long create(Klijent k)
            {
                try
                {
                    c = new MySqlCommand();
                    c.CommandText = "insert into klijenti values (@idKlijenta, @username, @ime, @prezime, @jmbg, @password, @statusKlijenta, @aktivan, @izbrisan)";
                    c.Parameters.AddWithValue("@idKlijenta", "");
                    c.Parameters.AddWithValue("@username", k.Username);
                    c.Parameters.AddWithValue("@ime", k.Ime);
                    c.Parameters.AddWithValue("@prezime", k.Prezime);
                    c.Parameters.AddWithValue("@jmbg", k.Jmbg);
                    c.Parameters.AddWithValue("@password", k.Password);
                    c.Parameters.AddWithValue("@statusKlijenta", Convert.ToString(k.statusKlijenta));
                    c.Parameters.AddWithValue("@aktivan", 0);
                    c.Parameters.AddWithValue("@izbrisan", 0);
                    c.Connection = con;
                    c.ExecuteNonQuery();
                    return c.LastInsertedId;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public List<Klijent> getAll()
            {
                c = new MySqlCommand("select * from klijenti where izbrisan=0", con);
                MySqlDataReader r = c.ExecuteReader();
                try
                {
                    List<Klijent> klijenti = new List<Klijent>();
                    while (r.Read())
                        klijenti.Add(new Klijent(r.GetInt32("idKlijenta"), r.GetString("username"), r.GetString("ime"), r.GetString("prezime"),
                                   r.GetString("jmbg"), r.GetString("password"), r.GetString("statusKlijenta"), r.GetBoolean("aktivan")));
                    return klijenti;
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    r.Close();
                }
            }


            public Klijent read(Klijent k)
            {
                try
                {
                    string commandStr = "select * from klijenti where idKlijenta=" + k.Id + " and izbrisan=0";
                    c = new MySqlCommand(commandStr, con);
                    MySqlDataReader r = c.ExecuteReader();
                    Klijent kl = null;
                    if (r.Read())
                        kl = new Klijent(r.GetInt32("idKlijenta"), r.GetString("username"), r.GetString("ime"), r.GetString("prezime"),
                                   r.GetString("jmbg"), r.GetString("password"), r.GetString("statusKlijenta"), r.GetBoolean("aktivan"));
                    r.Close();
                    return kl;
                }

                catch (Exception e)
                {
                    throw e;
                }
            }

            public Klijent update(Klijent k)
            {
                try
                {
                    string commandStr = "update klijenti set username='" + k.Username + "', ime='" +
                        k.Ime + "', prezime='" +
                        k.Prezime + "', jmbg='" + k.Jmbg + "', password='" + k.Password + "', statusKlijenta='" + k.statusKlijenta +
                        "', aktivan=" + k.Aktivan + " where idKlijenta=" + k.Id;


                    c = new MySqlCommand(commandStr, con);
                    c.ExecuteNonQuery();
                    return read(k);
                }
                catch (Exception e)
                {
                    throw e;
                }
            }

            public void delete(Klijent k)//brisanje postavlja kolonu "izbrisan" na 1
            {
                try
                {
                    string commandStr = "update klijenti set izbrisan=1 where idKlijenta=" + k.Id;
                    c = new MySqlCommand(commandStr, con);
                    c.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw e;
                }

            }

            public Klijent getById(int id)
            {
                Klijent k = new Klijent(id, "", "", "", "", "", "");
                return read(k);
            }

            public List<Klijent> getByExample(string name, string value)
            {
                try
                {
                    c = new MySqlCommand("select * from klijenti where izbrisan=0 and " + name + "= '" + value + "'", con);
                    MySqlDataReader r = c.ExecuteReader();
                    List<Klijent> klijenti = new List<Klijent>();
                    while (r.Read())
                        klijenti.Add(new Klijent(r.GetInt32("idKlijenta"), r.GetString("username"), r.GetString("ime"), r.GetString("prezime"),
                                   r.GetString("jmbg"), r.GetString("password"), r.GetString("statusKlijenta"), r.GetBoolean("aktivan")));

                    r.Close();
                    return klijenti;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            public int dajBrojPolica(Klijent k)
            {
                try
                {
                    string commandStr = "select count(*) from klijenti k, police p where k.idKlijenta=p.idKlijenta and k.idKlijenta=" + k.Id;
                    c = new MySqlCommand(commandStr, con);
                    return Convert.ToInt32(c.ExecuteScalar());
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
    }
}